--    Program: 		CAPAREXT_CUSTOMER_CONTACT_POINT.sql
--    Description:  Extract AR Customer Contact Point information
--    Revision:     1st     
--    Parameter:    1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		   number;
   l_rejected_count 		   number;
   l_err_msg				   varchar2(2000);
                    		
   l_utl_file       		   utl_file.file_type;
   l_filename       		   varchar2(255);
   l_pathname       		   varchar2(100);
   
   l_ngo_sob				   varchar2(20);
   l_set_of_books_id		   number;
   l_business_group_id		   number;
                    		       
   cursor cur_contact_point (p_business_group_id number) is   
      select hcp.* 
      from   hz_contact_points hcp, hz_party_sites hps
      where  hcp.owner_table_name = 'HZ_PARTY_SITES'
      and    hcp.owner_table_id = hps.party_site_id
      and    exists (select 'X' 
                     from   chz_parties cp
                     where  cp.s_business_group_id = p_business_group_id
			         and    cp.party_id = hps.party_id)
      union all
      select hcp.* 
      from   hz_contact_points hcp, hz_parties hp
      where  hcp.owner_table_name = 'HZ_PARTIES'
      and    hcp.owner_table_id = hp.party_id
      and    exists (select 'X' 
                     from   chz_parties cp
                     where  cp.s_business_group_id = p_business_group_id
			         and    cp.party_id = hp.party_id);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_CUSTOMER_CONTACT_POINT.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
   l_processed_count := 0;
   l_rejected_count  := 0;
      
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books short name not found');
       --raise;
   end; 
   
   begin
     select business_group_id
     into   l_business_group_id
     from   hr_organization_units 
     where  upper(name) = 'BG_'||upper(substr(l_ngo_sob, -4));
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': business group not found');
       raise;
   end;       

   for cur in cur_contact_point(l_business_group_id) loop   	
	  	  
  	  utl_file.put_line(l_utl_file,
        cur.CONTACT_POINT_ID              || chr(9) ||
        cur.CONTACT_POINT_TYPE            || chr(9) ||
        cur.STATUS                        || chr(9) ||
        cur.OWNER_TABLE_NAME              || chr(9) ||
        cur.OWNER_TABLE_ID                || chr(9) ||
        cur.PRIMARY_FLAG                  || chr(9) ||
        cur.ORIG_SYSTEM_REFERENCE         || chr(9) ||
        cur.LAST_UPDATE_DATE              || chr(9) ||
        cur.LAST_UPDATED_BY               || chr(9) ||
        cur.CREATION_DATE                 || chr(9) ||
        cur.CREATED_BY                    || chr(9) ||
        cur.LAST_UPDATE_LOGIN             || chr(9) ||
        cur.WH_UPDATE_DATE                || chr(9) ||
        cur.REQUEST_ID                    || chr(9) ||
        cur.PROGRAM_APPLICATION_ID        || chr(9) ||
        cur.PROGRAM_ID                    || chr(9) ||
        cur.PROGRAM_UPDATE_DATE           || chr(9) ||
        cur.ATTRIBUTE_CATEGORY            || chr(9) ||
        cur.ATTRIBUTE1                    || chr(9) ||
        cur.ATTRIBUTE2                    || chr(9) ||
        cur.ATTRIBUTE3                    || chr(9) ||
        cur.ATTRIBUTE4                    || chr(9) ||
        cur.ATTRIBUTE5                    || chr(9) ||
        cur.ATTRIBUTE6                    || chr(9) ||
        cur.ATTRIBUTE7                    || chr(9) ||
        cur.ATTRIBUTE8                    || chr(9) ||
        cur.ATTRIBUTE9                    || chr(9) ||
        cur.ATTRIBUTE10                   || chr(9) ||
        cur.ATTRIBUTE11                   || chr(9) ||
        cur.ATTRIBUTE12                   || chr(9) ||
        cur.ATTRIBUTE13                   || chr(9) ||
        cur.ATTRIBUTE14                   || chr(9) ||
        cur.ATTRIBUTE15                   || chr(9) ||
        cur.ATTRIBUTE16                   || chr(9) ||
        cur.ATTRIBUTE17                   || chr(9) ||
        cur.ATTRIBUTE18                   || chr(9) ||
        cur.ATTRIBUTE19                   || chr(9) ||
        cur.ATTRIBUTE20                   || chr(9) ||
        cur.GLOBAL_ATTRIBUTE_CATEGORY     || chr(9) ||
        cur.GLOBAL_ATTRIBUTE1             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE2             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE3             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE4             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE5             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE6             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE7             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE8             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE9             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE10            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE11            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE12            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE13            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE14            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE15            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE16            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE17            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE18            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE19            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE20            || chr(9) ||
        cur.EDI_TRANSACTION_HANDLING      || chr(9) ||
        cur.EDI_ID_NUMBER                 || chr(9) ||
        cur.EDI_PAYMENT_METHOD            || chr(9) ||
        cur.EDI_PAYMENT_FORMAT            || chr(9) ||
        cur.EDI_REMITTANCE_METHOD         || chr(9) ||
        cur.EDI_REMITTANCE_INSTRUCTION    || chr(9) ||
        cur.EDI_TP_HEADER_ID              || chr(9) ||
        cur.EDI_ECE_TP_LOCATION_CODE      || chr(9) ||
        cur.EMAIL_FORMAT                  || chr(9) ||
        cur.EMAIL_ADDRESS                 || chr(9) ||
        cur.BEST_TIME_TO_CONTACT_START    || chr(9) ||
        cur.BEST_TIME_TO_CONTACT_END      || chr(9) ||
        cur.PHONE_CALLING_CALENDAR        || chr(9) ||
        cur.CONTACT_ATTEMPTS              || chr(9) ||
        cur.CONTACTS                      || chr(9) ||
        cur.DECLARED_BUSINESS_PHONE_FLAG  || chr(9) ||
        cur.DO_NOT_USE_FLAG               || chr(9) ||
        cur.DO_NOT_USE_REASON             || chr(9) ||
        cur.LAST_CONTACT_DT_TIME          || chr(9) ||
        cur.PHONE_PREFERRED_ORDER         || chr(9) ||
        cur.PRIORITY_OF_USE_CODE          || chr(9) ||
        cur.TELEPHONE_TYPE                || chr(9) ||
        cur.TIME_ZONE                     || chr(9) ||
        cur.PHONE_TOUCH_TONE_TYPE_FLAG    || chr(9) ||
        cur.PHONE_AREA_CODE               || chr(9) ||
        cur.PHONE_COUNTRY_CODE            || chr(9) ||
        cur.PHONE_NUMBER                  || chr(9) ||
        cur.PHONE_EXTENSION               || chr(9) ||
        cur.PHONE_LINE_TYPE               || chr(9) ||
        cur.TELEX_NUMBER                  || chr(9) ||
        cur.WEB_TYPE                      || chr(9) ||
        cur.URL                           || chr(9) ||
        cur.CONTENT_SOURCE_TYPE           || chr(9) ||
        cur.RAW_PHONE_NUMBER              || chr(9) ||
        cur.OBJECT_VERSION_NUMBER         || chr(9) ||
        cur.CREATED_BY_MODULE             || chr(9) ||
        cur.APPLICATION_ID                || chr(9) ||
        cur.TIMEZONE_ID                   || chr(9) ||
        cur.CONTACT_POINT_PURPOSE         || chr(9) ||
        cur.PRIMARY_BY_PURPOSE            || chr(9) ||
        cur.TRANSPOSED_PHONE_NUMBER       || chr(9) ||
        cur.EFT_TRANSMISSION_PROGRAM_ID   || chr(9) ||
        cur.EFT_PRINTING_PROGRAM_ID       || chr(9) ||
        cur.EFT_USER_NUMBER               || chr(9) ||
        cur.EFT_SWIFT_CODE                || chr(9) ||
        cur.ACTUAL_CONTENT_SOURCE      
	   );
       	        
	  l_processed_count := l_processed_count + 1;
   end loop;
   
   l_err_msg := '';
   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Customer Contact Point not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_CUSTOMER_CONTACT_POINT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/     


--exit;
